#!/bin/bash
# File Name: diff_count.sh
# Author: moshan
# mail: mo_shan@yeah.net
# Created Time: 2019-02-22 16:46:26
# Function: 对比master与slave的数据行是否一致
#########################################################################
:<<comment
变量注释:
db_name       : 业务库名，一般为'test'，要根据实际情况配置;
mysql_user    : 连接mysql用户名;
mysql_passwd  : 连接mysql的密码;
mysql_port1   : 第一个节点的端口号;
mysql_port2   : 第二个节点的端口号;
log_file      : 日志文件;
mysql_host1   : 第一个节点的ip地址
mysql_host2   : 第二个节点的ip地址
check_mode    : 可以指定检查算法, 默认使用count来统计数据行, 也可以传递一个checksumx参数, bash diff_count.sh checksumx
注意：默认以第一个节点为参照，即程序会先获取到第一个节点的用户列表
comment
db_name="test"
mysql_user="mysql_user"
mysql_passwd="mysql_passwd"
log_file="count_result.log"
mysql_port1="xxxx"
mysql_port2="xxxx"
mysql_host1="192.168.1.xxx"
mysql_host2="192.168.1.xxx"
 
 
#以下无需编辑修改
check_mode="${1}"
mysql_port="${mysql_port1}"
host_list=(${mysql_host1} ${mysql_host2})
mysql_comm="mysql -u${mysql_user} -p${mysql_passwd} -h${host_list[0]} -P${mysql_port} ${db_name}"
${mysql_comm} -NBe "select 1;" >/dev/null 2>&1
if [ $? -ne 0 ]
then
        echo
        echo -e "\033[31m[$(date "+%F %H:%M:%S")] [ERROR] Can't connect to MySQL server on '${mysql_user}@${mysql_host1}' for mysql_port:${mysql_port}\033[0m"
        echo
        exit 1
else
        >${log_file}
fi
table=($(${mysql_comm} -NBe "show tables" 2>/dev/null|grep -v "_old$"))
for ((i=0;i<${#table[@]};i++))
do
        col_str="$(${mysql_comm} -NBe "desc ${table[${i}]};" 2>/dev/null|awk '{print $1}'|tr "\n" ",")"
        col_str_isnull="$(${mysql_comm} -NBe "desc ${table[${i}]};" 2>/dev/null|awk '{print "ISNULL("$1")"}'|tr "\n" ","|sed 's#\(.*\),$#\1#g')"
        for ((j=0;j<${#host_list[@]};j++))
        do
        {
                if [ "${j}x" == "0x" ]
                then
                        mysql_port="${mysql_port1}"
                else
                        mysql_port="${mysql_port2}"
                fi
                mysql_comm="mysql -u${mysql_user} -p${mysql_passwd} -h${host_list[${j}]} -P${mysql_port} ${db_name}"
                if [ "${check_mode}x" == "checksumx" ]
                then
                        ${mysql_comm} -NBe "SELECT '${table[${i}]}', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#',${col_str}CONCAT(${col_str_isnull}))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM  ${table[${i}]};" 2>/dev/null|awk '{print $1":"$2":"$3}' > ${host_list[${j}]}_${mysql_port}.log
                else
                        ${mysql_comm} -NBe "SELECT '${table[${i}]}', COUNT(*),'checksum' FROM  ${table[${i}]};" 2>/dev/null|awk '{print $1":"$2":"$3}' > ${host_list[${j}]}_${mysql_port}.log
                fi
        }&
        done
        wait
        if [ "$(md5sum ${host_list[0]}_${mysql_port1}.log|awk '{print $1}')x" == "$(md5sum ${host_list[1]}_${mysql_port2}.log|awk '{print $1}')x" ]
        then
                echo -e "\033[32m[$(date "+%F %H:%M:%S")] [OK] ${table[${i}]}:数据一致。\033[0m"
        else
                echo -e "\033[31m[$(date "+%F %H:%M:%S")] [ERROR] ${table[${i}]}:数据不一致。 第一个节点的数据行为：$(awk -F: '{print $2}' ${host_list[0]}_${mysql_port1}.log)，第二个节点的数据行为：$(awk -F: '{print $2}' ${host_list[1]}_${mysql_port2}.log)。\033[0m"
        fi
        echo "[$(date "+%F %H:%M:%S")] ${host_list[0]}:${mysql_port1} $(cat ${host_list[0]}_${mysql_port1}.log)" >> ${log_file}
        echo "[$(date "+%F %H:%M:%S")] ${host_list[1]}:${mysql_port2} $(cat ${host_list[1]}_${mysql_port2}.log)" >> ${log_file}
        [ -f "${host_list[0]}_${mysql_port1}.log" ] && rm -f ${host_list[0]}_${mysql_port1}.log
        [ -f "${host_list[1]}_${mysql_port2}.log" ] && rm -f ${host_list[0]}_${mysql_port1}.log
done
